﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Server;


public partial class ClrSmartDML
{


[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,SystemDataAccess=SystemDataAccessKind.Read)]
public static SqlBoolean fn_smart_delete(SqlString object_name, SqlString object_schema, SqlString object_db, SqlString where_clause, SqlInt16 threshold_percent, SqlInt32 large_table_threshold, SqlInt32 comit_size )
{
    // declare/initialize variables 
    String sSQL = String.Empty;
	int iRowCnt = 0;
	int iDelCnt = 0 ;


    // establish connection 
    SqlConnection SqlConn = new SqlConnection("context connection=true");
    using (SqlCommand SqlCmd = new SqlCommand())
    {
        SqlConn.Open();
        SqlCmd.Connection = SqlConn;

        // determine if there are any rows to delete
        // get rowcount
        SqlCmd.CommandText = " SELECT 'RowCnt' = SUM(p.[rows]) "
                           + " FROM " + object_db.ToString() + ".sys.partitions p WITH (NOLOCK) " 
                           + " WHERE p.index_id IN (1,0) "
                           + " AND p.[object_id] = OBJECT_ID([" + object_db.ToString() + "].[" + object_schema.ToString() + "].[" + object_name.ToString() + "]) "
                           + " GROUP BY p.[object_id] ";
        iRowCnt = (int)SqlCmd.ExecuteScalar();

        // if empty - nothing to do EXIT
        if (iRowCnt == 0)
            return true;

        SqlCmd.CommandText = " SELECT 'DelCnt' = COUNT(*) FROM (SELECT TOP 1 NULL "
                           + " FROM [" + object_db.ToString() + "].[" + object_schema.ToString() + "].[" + object_name.ToString() + "] WITH (NOLOCK) "
                           + " WHERE (" + where_clause.ToString() + ")) DTBL";
        iDelCnt = (int)SqlCmd.ExecuteScalar();

        // if no rows to delete - nothing to do EXIT
        if (iDelCnt == 0)
            return true;

        // check if truncate possible instead
        SqlCmd.CommandText = " SELECT 'DelCnt' = COUNT(*) FROM (SELECT TOP 1 NULL "
                           + " FROM [" + object_db.ToString() + "].[" + object_schema.ToString() + "].[" + object_name.ToString() + "] WITH (NOLOCK) "
                           + " WHERE NOT (" + where_clause.ToString() + ")) DTBL";
        iDelCnt = (int)SqlCmd.ExecuteScalar();

        if (iDelCnt == 0)
        {
            SqlCmd.CommandText = " TRUNCATE TABLE [" + object_db.ToString() + "].[" + object_schema.ToString() + "].[" + object_name.ToString() + "]";
            if (SqlCmd.ExecuteNonQuery() > 0)
                return true;
            else
                return false;
        }



    }

    return false;
}
// end fn_object_exists


[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString fn_object_drop(SqlInt64 object_id)
{
    // declare/initialize variables 
    String db_name = String.Empty;

    SqlConnection SqlConn = new SqlConnection("context connection=true");
    using (SqlCommand SqlCmd = new SqlCommand())
    {
        SqlConn.Open();
        SqlCmd.Connection = SqlConn;
        SqlCmd.CommandText = " SELECT 'result' = DB_NAME()";
        db_name = SqlCmd.ExecuteScalar().ToString();
    }

    return db_name;
}
// end fn_object_drop

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString fn_object_type(SqlInt64 object_id)
{
    // declare/initialize variables 
    String db_name = String.Empty;

    SqlConnection SqlConn = new SqlConnection("context connection=true");
    using (SqlCommand SqlCmd = new SqlCommand())
    {
        SqlConn.Open();
        SqlCmd.Connection = SqlConn;
        SqlCmd.CommandText = " SELECT 'result' = DB_NAME()";
        db_name = SqlCmd.ExecuteScalar().ToString();
    }

    return db_name;
}
// end fn_object_type

    
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString fn_object_dbname(SqlInt64 object_id)
    {
        // declare/initialize variables 
        String db_name = String.Empty;

        SqlConnection SqlConn = new SqlConnection("context connection=true");
        using (SqlCommand SqlCmd = new SqlCommand())
        {
            SqlConn.Open();
            SqlCmd.Connection = SqlConn;
            SqlCmd.CommandText = " SELECT 'result' = DB_NAME()";
            db_name = SqlCmd.ExecuteScalar().ToString();
        }

        return db_name;
    }
    // end fn_object_dbname

    

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString fn_object_definition(SqlInt64 object_id, SqlInt64 database_id)
    {
        // declare/initialize variables 
        String obj_db = String.Empty;
        String obj_schema = String.Empty;
        String obj_name = String.Empty;
        String obj_type = String.Empty;
        String obj_def = String.Empty; 

        SqlConnection SqlConn = new SqlConnection("context connection=true");
        using (SqlCommand SqlCmd = new SqlCommand())
        {
            SqlConn.Open();
            SqlCmd.Connection = SqlConn;
            SqlCmd.CommandText = " SELECT 'result' = DB_NAME(" + database_id.ToString() + ")";
            obj_db = SqlCmd.ExecuteScalar().ToString();


            // if invalid db id exit
            if (database_id <= 0 || obj_db == null)
            {
                return null;
            } /*end if (database_id <= 0 || obj_db == null)*/


            // check if native SQL function OBJECT_DEFINITION works
            SqlCmd.CommandText = "SELECT 'result' = OBJECT_DEFINITION(" + object_id.ToString() + ");";
            obj_def = SqlCmd.ExecuteScalar().ToString();

            //check if OBJECT_DEFINITION retuned null
            if (obj_def == null)
            {
                SqlCmd.CommandText = "SELECT 'result' = [schema] FROM " + obj_db.ToString() + ".sys.all_objects WHERE id = " + object_id.ToString();
                obj_schema = SqlCmd.ExecuteScalar().ToString();
            }


            //SqlCmd.CommandText = "SELECT 'result' = [schema] FROM " + obj_db.ToString() + ".sys.all_objects WHERE id = " + object_id.ToString();
            //obj_schema = SqlCmd.ExecuteScalar().ToString();

            //SqlCmd.CommandText = "SELECT 'result' = [name] FROM " + obj_db.ToString() + ".sys.all_objects WHERE id = " + object_id.ToString();
            //obj_name = SqlCmd.ExecuteScalar().ToString();

            return obj_def;
        }
    }
    // end fn_object_definition


[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString fn_table_definition(SqlString table_name, SqlString table_schema, SqlString table_db, SqlString table_filegroup)
{
    return "";
}

};


